"""
枚举数据CRUD操作
包含枚举类型和枚举条目的增删改查
"""
import json
from typing import List, Optional, Dict, Any

from app.models.enum_models import EnumType, EnumItem, EnumGroup, EnumMap
from app.schemas.enum_schemas import EnumItemCreate, EnumItemUpdate
from common.database import DatabaseService
from common.exceptions import BizException


# ---------- 枚举类型操作 ----------

async def get_enum_type_by_name(db: DatabaseService, type_name: str) -> Optional[EnumType]:
    """
    根据类型名称获取枚举类型

    Args:
        db: 数据库服务
        type_name: 枚举类型名称

    Returns:
        EnumType: 枚举类型对象，不存在则返回None
    """
    sql = "SELECT id, type, description FROM base_enum_type WHERE type = %s"
    row = db.get_one(sql, (type_name,))
    return EnumType(**row) if row else None


async def create_enum_type(db: DatabaseService, type_name: str, description: str = None) -> int:
    """
    创建新的枚举类型

    Args:
        db: 数据库服务
        type_name: 枚举类型名称
        description: 类型描述

    Returns:
        int: 新创建的枚举类型ID

    Raises:
        BizException: 类型名称已存在时抛出ENUM_TYPE_EXISTS错误
    """
    # 检查是否已存在
    existing = await get_enum_type_by_name(db, type_name)
    if existing:
        raise BizException.enum_type_exists(type_name)

    sql = "INSERT INTO base_enum_type (type, description) VALUES (%s, %s)"
    return db.insert(sql, (type_name, description))


# ---------- 枚举条目操作 ----------

async def get_enum_items_by_type(db: DatabaseService, type_name: str) -> List[EnumItem]:
    """
    根据类型名称获取所有枚举条目

    Args:
        db: 数据库服务
        type_name: 枚举类型名称

    Returns:
        List[EnumItem]: 枚举条目列表
    """
    sql = """
          SELECT ei.id,
                 ei.type_id,
                 et.type,
                 ei.value,
                 ei.label,
                 ei.sort_order,
                 ei.status,
                 ei.ext_json,
                 ei.gmt_create
          FROM base_enum_item ei
                   JOIN base_enum_type et ON ei.type_id = et.id
          WHERE et.type = %s
          ORDER BY ei.sort_order ASC, ei.id ASC \
          """
    rows = db.get_all(sql, (type_name,))
    return [EnumItem(**_parse_enum_item_row(row)) for row in rows]


async def get_enum_item_by_id(db: DatabaseService, item_id: int) -> Optional[EnumItem]:
    """
    根据ID获取枚举条目

    Args:
        db: 数据库服务
        item_id: 枚举条目ID

    Returns:
        EnumItem: 枚举条目对象，不存在则返回None
    """
    sql = """
          SELECT ei.id,
                 ei.type_id,
                 et.type,
                 ei.value,
                 ei.label,
                 ei.sort_order,
                 ei.status,
                 ei.ext_json,
                 ei.gmt_create
          FROM base_enum_item ei
                   JOIN base_enum_type et ON ei.type_id = et.id
          WHERE ei.id = %s \
          """
    row = db.get_one(sql, (item_id,))
    return EnumItem(**_parse_enum_item_row(row)) if row else None


async def get_enum_map_by_types(db: DatabaseService, type_names: List[str]) -> Dict[str, List[EnumMap]]:
    """
    批量获取多个枚举类型的映射数据

    Args:
        db: 数据库服务
        type_names: 枚举类型名称列表

    Returns:
        Dict[str, List[EnumMap]]: 以类型名为key的枚举映射字典
    """
    if not type_names:
        return {}

    placeholders = ','.join(['%s'] * len(type_names))
    sql = f"""
        SELECT et.type, ei.value, ei.label, ei.status, ei.ext_json
        FROM base_enum_item ei
        JOIN base_enum_type et ON ei.type_id = et.id
        WHERE et.type IN ({placeholders}) AND ei.status = 1
        ORDER BY et.type ASC, ei.sort_order ASC, ei.id ASC
    """

    rows = db.get_all(sql, tuple(type_names))

    # 按类型分组
    result = {type_name: [] for type_name in type_names}
    for row in rows:
        enum_map = EnumMap(
            value=row['value'],
            label=row['label'],
            disabled=row['status'] == 0,
            ext=json.loads(row['ext_json']) if row['ext_json'] else None
        )
        result[row['type']].append(enum_map)

    return result


async def create_enum_item(db: DatabaseService, payload: EnumItemCreate) -> EnumItem:
    """
    创建枚举条目

    Args:
        db: 数据库服务
        payload: 创建请求数据

    Returns:
        EnumItem: 创建的枚举条目

    Raises:
        BizException: 类型不存在或同类型下值已存在时抛出错误
    """
    # 获取或创建枚举类型
    enum_type = await get_enum_type_by_name(db, payload.type)
    if not enum_type:
        # 自动创建枚举类型
        type_id = await create_enum_type(db, payload.type)
    else:
        type_id = enum_type.id

    # 检查同类型下值是否已存在
    check_sql = """
                SELECT COUNT(*) as count
                FROM base_enum_item ei
                    JOIN base_enum_type et
                ON ei.type_id = et.id
                WHERE et.type = %s
                  AND ei.value = %s \
                """
    count_row = db.get_one(check_sql, (payload.type, payload.value))
    if count_row and count_row['count'] > 0:
        raise BizException.enum_value_exists(payload.type, payload.value)

    # 插入枚举条目
    insert_sql = """
                 INSERT INTO base_enum_item (type_id, value, label, sort_order, status, ext_json)
                 VALUES (%s, %s, %s, %s, %s, %s) \
                 """
    ext_json_str = json.dumps(payload.ext_json) if payload.ext_json else None

    item_id = db.insert(insert_sql, (
        type_id,
        payload.value,
        payload.label,
        payload.sort_order,
        payload.status,
        ext_json_str
    ))

    # 返回创建的枚举条目
    return await get_enum_item_by_id(db, item_id)


async def update_enum_item(db: DatabaseService, item_id: int, payload: EnumItemUpdate) -> Optional[EnumItem]:
    """
    更新枚举条目

    Args:
        db: 数据库服务
        item_id: 枚举条目ID
        payload: 更新请求数据

    Returns:
        EnumItem: 更新后的枚举条目，不存在则返回None

    Raises:
        BizException: 更新的值在同类型下已存在时抛出ENUM_VALUE_EXISTS错误
    """
    # 检查条目是否存在
    existing = await get_enum_item_by_id(db, item_id)
    if not existing:
        return None

    # 构建更新字段
    update_fields = []
    update_values = []

    if payload.value is not None:
        # 检查新值是否与其他条目冲突
        if payload.value != existing.value:
            check_sql = """
                        SELECT COUNT(*) as count
                        FROM base_enum_item ei
                            JOIN base_enum_type et
                        ON ei.type_id = et.id
                        WHERE et.type = %s
                          AND ei.value = %s
                          AND ei.id != %s \
                        """
            count_row = db.get_one(check_sql, (existing.type, payload.value, item_id))
            if count_row and count_row['count'] > 0:
                raise BizException.enum_value_exists(existing.type, payload.value)

        update_fields.append("value = %s")
        update_values.append(payload.value)

    if payload.label is not None:
        update_fields.append("label = %s")
        update_values.append(payload.label)

    if payload.sort_order is not None:
        update_fields.append("sort_order = %s")
        update_values.append(payload.sort_order)

    if payload.status is not None:
        update_fields.append("status = %s")
        update_values.append(payload.status)

    if payload.ext_json is not None:
        update_fields.append("ext_json = %s")
        update_values.append(json.dumps(payload.ext_json) if payload.ext_json else None)

    if not update_fields:
        # 没有字段需要更新
        return existing

    # 执行更新
    update_sql = f"UPDATE base_enum_item SET {', '.join(update_fields)} WHERE id = %s"
    update_values.append(item_id)

    affected_rows = db.update(update_sql, tuple(update_values))
    if affected_rows == 0:
        return None

    # 返回更新后的数据
    return await get_enum_item_by_id(db, item_id)


async def delete_enum_item(db: DatabaseService, item_id: int) -> bool:
    """
    删除枚举条目

    Args:
        db: 数据库服务
        item_id: 枚举条目ID

    Returns:
        bool: 删除成功返回True，条目不存在返回False
    """
    sql = "DELETE FROM base_enum_item WHERE id = %s"
    affected_rows = db.delete(sql, (item_id,))
    return affected_rows > 0


async def get_all_enum_types(db: DatabaseService) -> List[str]:
    """
    获取所有枚举类型名称列表

    Args:
        db: 数据库服务

    Returns:
        List[str]: 枚举类型名称列表
    """
    sql = "SELECT DISTINCT type FROM base_enum_type ORDER BY type ASC"
    rows = db.get_all(sql)
    return [row['type'] for row in rows]


async def get_enum_groups_by_types(db: DatabaseService, type_names: List[str]) -> List[EnumGroup]:
    """
    按类型分组获取枚举数据

    Args:
        db: 数据库服务
        type_names: 枚举类型名称列表

    Returns:
        List[EnumGroup]: 枚举分组列表
    """
    groups = []
    for type_name in type_names:
        # 获取类型信息
        enum_type = await get_enum_type_by_name(db, type_name)
        if not enum_type:
            continue

        # 获取该类型下的所有条目
        items = await get_enum_items_by_type(db, type_name)

        groups.append(EnumGroup(
            type=enum_type.type,
            description=enum_type.description,
            items=items
        ))

    return groups


def _parse_enum_item_row(row: Dict[str, Any]) -> Dict[str, Any]:
    """
    解析数据库行数据为EnumItem模型数据

    Args:
        row: 数据库查询结果行

    Returns:
        Dict[str, Any]: 解析后的数据
    """
    parsed = dict(row)

    # 解析JSON字段
    if 'ext_json' in parsed and parsed['ext_json']:
        try:
            parsed['ext_json'] = json.loads(parsed['ext_json'])
        except json.JSONDecodeError:
            parsed['ext_json'] = None

    return parsed
